/***********************************************************
 * Product Name: ExcelQuicker
 * Copyright (c)2005, Eunge
 * Author: Eunge
 * Web Site: www.eudev.net
 * Mail/MSN: eudev.net@yeah.net
 * ********************************************************/
using System.Data;
using System;
using System.Collections;

namespace ExcelQuicker.Framework
{
	/// <summary>
	/// Pivot table. Datasource from datatable. Use this class to achieve off-line analysis.
	/// </summary>
	[Serializable]
	public sealed class EQPivotTable: EQTableBase  	
	{
		#region Attibutes
		private string mPivotTableName = "";
		private string[] mPivotFieldHeaders;
		private EQPivotField[] mFieldCollection;

		/// <summary>
		/// Set/Get field collection
		/// </summary>
		private EQPivotField[] FieldCollection
		{
			get
			{
				return this.mFieldCollection;
			}
			set
			{
				this.mFieldCollection = value;
			}
		}

		/// <summary>
		/// Set/Get header of pivot field. If you didn't set before, the component will take column name of datatable as default value
		/// </summary>
		private string[] PivotFieldHeaders
		{
			get
			{
				return this.mPivotFieldHeaders;
			}
			set
			{
				this.mPivotFieldHeaders = value;
			}
		}

		/// <summary>
		/// Get first column index on excel document
		/// </summary>
		public override int ItemLeft
		{
			get
			{
				return this.TableLeft;
			}
		}

		/// <summary>
		/// Get first row index on excel document
		/// </summary>
		public override int ItemTop
		{
			get
			{
				int pageFieldCount = this.PageFieldCount;
				return (pageFieldCount == 0)?this.TableTop:this.TableTop + pageFieldCount + 1;
			}
		}

		/// <summary>
		/// Get pivot table name
		/// </summary>
		public string PivotTableName
		{
			get
			{
				return this.mPivotTableName;
			}
		}

		/// <summary>
		/// Get the count of page field
		/// </summary>
		public int PageFieldCount
		{
			get
			{
				return this.GetFieldCount(Excel.XlPivotFieldOrientation.xlPageField);
			}
		}

		/// <summary>
		/// Get the count of column field
		/// </summary>
		public int ColumnFieldCount
		{
			get
			{
				return this.GetFieldCount(Excel.XlPivotFieldOrientation.xlColumnField);
			}
		}

		/// <summary>
		/// Get the count of row field
		/// </summary>
		public int RowFieldCount
		{
			get
			{
				return this.GetFieldCount(Excel.XlPivotFieldOrientation.xlRowField);
			}
		}

		/// <summary>
		/// Get the count of data field
		/// </summary>
		public int DataFieldCount
		{
			get
			{
				return this.GetFieldCount(Excel.XlPivotFieldOrientation.xlDataField);
			}
		}

		/// <summary>
		/// Get the count of hidden field
		/// </summary>
		public int HiddenFieldCount
		{
			get
			{
				return this.GetFieldCount(Excel.XlPivotFieldOrientation.xlHidden);
			}
		}

		/// <summary>
		/// Set/Get the binding chart of this pivot table
		/// </summary>
		public new EQPivotChart Chart
		{
			get
			{
				return base.Chart as EQPivotChart;
			}
			set
			{
				base.Chart = value;
			}
		}

		#endregion

		/// <summary>
		/// Get amount of specialized type fields
		/// </summary>
		/// <param name="type"></param>
		/// <returns></returns>
		private int GetFieldCount(Excel.XlPivotFieldOrientation type)
		{
			int count = 0;
			IEnumerator objIEnumerator = this.FieldCollection.GetEnumerator();
			while(objIEnumerator.MoveNext())
			{
				if((objIEnumerator.Current as EQPivotField).PivotFieldOrientation == type)
					count ++;
			}
			return count;
		}


		/// <summary>
		/// Return the EQPivotField object which its field name is pivotFieldName
		/// </summary>
		///<param name="pivotFieldName">Pivot field name</param>
		public EQPivotField GetPivotField(string pivotFieldName)
		{
			if(!this.CheckValidPivotFieldName(pivotFieldName))
				throw new EQException("EQ1017", pivotFieldName, EQKit.Concat(this.TableColumnsName));	//_pivotFieldName must belong to collection of column name of datatable

			for(int i=0; i<this.FieldCollection.Length; i++)
			{
				if(this.FieldCollection[i].PivotFieldName == pivotFieldName)
					return this.FieldCollection[i];
			}
			return null;
		}


		/// <summary>
		/// Bind the objEQPivotField object with pivot field named parameter "pivotFieldName"
		/// </summary>
		/// <param name="pivotFieldName"></param>
		/// <param name="objEQPivotField"></param>
		/// <example>
		/// <br>EQPivotField objEQPivotField = new EQPivotField(objEQPivotTable.TableColumnsName[5]);</br>
		/// <br>objEQPivotField.PivotFieldOrientation = Excel.XlPivotFieldOrientation.xlDataField;</br>
		/// <br>objEQPivotField.Priority = 1;</br>
		/// <br>objEQPivotField.SubtotalsName = "Sales Achievement";</br>
		/// <br>objEQPivotTable.SetPivotField(objEQPivotTable.TableColumnsName[5], objEQPivotField); //Assume that we have defined objEQPivotTable before</br>
		/// </example>
		public void SetPivotField(string pivotFieldName, EQPivotField objEQPivotField)
		{
			this[pivotFieldName] = objEQPivotField;
		}


		/// <summary>
		/// Set/Get EQPivotField object by its pivot field name. 	The parameter pivotFieldName must belong to the collection of datatable's column name
		/// </summary>
		public EQPivotField this[string pivotFieldName]
		{
			get
			{
				if(!this.CheckValidPivotFieldName(pivotFieldName))
					throw new EQException("EQ1017", pivotFieldName, EQKit.Concat(this.TableColumnsName));	//pivotFieldName must belong to the collection of datatable's column name

				return this.GetPivotField(pivotFieldName);
			}
			set
			{
				if(!this.CheckValidPivotFieldName(pivotFieldName))
					throw new EQException("EQ1017", pivotFieldName, EQKit.Concat(this.TableColumnsName));		//pivotFieldName must belong to the collection of datatable's column name

				if(value.PivotFieldName != pivotFieldName)
					throw new EQException("EQ1018", value.PivotFieldName, pivotFieldName);	//The PivotFieldName of EQPivotField object must be equal to pivotFieldName

				//EQPivotField objEQPivotField = this.GetPivotField(_pivotFieldName);
				for(int i=0; i<this.FieldCollection.Length; i++)
				{
					if(this.FieldCollection[i].PivotFieldName == pivotFieldName)
						this.FieldCollection[i] = value;
				}
			}
		}


		/// <summary>
		/// Check whether parameter fieldName is contained in the column names of datatable
		/// </summary>
		/// <param name="fieldName"></param>
		/// <returns></returns>
		private bool CheckValidPivotFieldName(string fieldName)
		{
			foreach(string strFieldName in this.TableColumnsName)
			{
				if(strFieldName == fieldName)
				{
					return true;
				}
			}
			return false;
		}


		/// <summary>
		/// Initialize object
		/// </summary>
		/// <param name="pivotTableName">Name of pivot table. Must be unique.</param>
		/// <param name="objDataTable"></param>
		/// <param name="top"></param>
		/// <param name="left"></param>
		public EQPivotTable(string pivotTableName, DataTable objDataTable, int top, int left):base(objDataTable, top, left)
		{
			if(pivotTableName.Trim().Length == 0)
				throw new EQException("EQ1019");	//Cannot be empty

			this.mPivotTableName = pivotTableName;
			this.PivotFieldHeaders = this.TableColumnsName;

			this.FieldCollection = new EQPivotField[this.TableColumnsCount];	//Create several fields
			for(int i=0; i<this.TableColumnsCount; i++)
				this.FieldCollection[i] = new EQPivotField(this.TableColumnsName[i]);
		}


		/// <summary>
		/// Initialize object
		/// </summary>
		/// <param name="pivotTableName">Name of pivot table. Must be unique.</param>
		/// <param name="pivotFieldHeaders">Headers of pivot table</param>
		/// <param name="objDataTable"></param>
		/// <param name="top"></param>
		/// <param name="left"></param>
		public EQPivotTable(string pivotTableName, string[] pivotFieldHeaders, DataTable objDataTable, int top, int left):this(pivotTableName, objDataTable, top, left)
		{
			if(pivotFieldHeaders.Length != objDataTable.Columns.Count)
				throw new EQException("EQ1020", pivotFieldHeaders.Length.ToString(EQKit.ObjNumberFormatInfo), objDataTable.Columns.Count.ToString(EQKit.ObjNumberFormatInfo));	//The amount of pivot table headers must be completely equal to the columns of datatable.
		}


		/// <summary>
		/// Output pivot table to excel document
		/// </summary>
		/// <param name="objWorksheet"></param>
		protected internal override void Output(Excel.Worksheet objWorksheet)
		{
			this.DoBeforeOperationEvent(objWorksheet);
			/**************************************************
			 * Arithmetic Description:
			 * 1, Create a temporary worksheet first, and use batch arithmetic to output items
			 * 2, Create a pivot table based on items on the temporary worksheet
			 * 3, Set relative pivot fields
			 * 4, Set their style
			 * 5, Delete the temporary worksheet
			 *************************************************/

			//Create a temporary worksheet first, and activate it.
			Excel.Worksheet objTempWorksheet = (Excel.Worksheet)objWorksheet.Application.Worksheets.Add(this.Missing, this.Missing, this.Missing, this.Missing);
			objTempWorksheet.Activate();
			
			//Use batch arithmetic to output items
			Excel.Range objTempRange = (Excel.Range)objTempWorksheet.get_Range(objTempWorksheet.Cells[2,1], objTempWorksheet.Cells[this.Table.Rows.Count + 1, this.Table.Columns.Count]);
			this.BatchModeObject.ArithmeticOutput(objTempRange, this);

			//Set area of headers
			objTempRange = (Excel.Range)objTempWorksheet.get_Range(objTempWorksheet.Cells[1,1], objTempWorksheet.Cells[1, this.Table.Columns.Count]);
			objTempRange.Value = this.PivotFieldHeaders;

			//Select wholepivot table area, including data and headers
			string strPivotTableDataSource = objTempWorksheet.Name + "!";
			strPivotTableDataSource += "R1C1:R" + (this.Table.Rows.Count + 1).ToString(EQKit.ObjNumberFormatInfo) + "C" + this.Table.Columns.Count.ToString(EQKit.ObjNumberFormatInfo);

			//Create pivot table on temporary worksheet
			objWorksheet.Activate();
			Excel.Range objRange = (Excel.Range)objWorksheet.Cells[this.ItemTop, this.ItemLeft];	//Get the position of destination cell
			Excel.PivotCaches objPivotCaches = objWorksheet.Application.ActiveWorkbook.PivotCaches();
			Excel.PivotCache objPivotCache = objPivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, strPivotTableDataSource);
			Excel.PivotTable objXlPivotTable = objPivotCache.CreatePivotTable(objRange, this.mPivotTableName, this.Missing);	//Create pivot table
			
			//Create several pivot fields of pivot table
			this.CreateXlPivotField(objXlPivotTable);

			//Set style of each pivot field
			for(int i=0; i<this.FieldCollection.Length; i++)
			{
				Excel.PivotField objXlPivotField = objXlPivotTable.PivotFields(this.FieldCollection[i].PivotFieldName) as Excel.PivotField;
				this.SetPivotFieldStyle(objXlPivotField, this.FieldCollection[i]);
			}

			//Delete temporary worksheet
			objTempWorksheet.Delete();

			base.Output(objWorksheet);

			this.DoAfterOperationEvent(objWorksheet);
		}


		/// <summary>
		/// Create several pivot field of current pivot table
		/// </summary>
		/// <param name="objPivotTable"></param>
		private void CreateXlPivotField(Excel.PivotTable objPivotTable)
		{
			//Get the IEnumerator interface and go through all pivot fields
			IEnumerator objIEnumerator = this.FieldCollection.GetEnumerator();
			while(objIEnumerator.MoveNext())
			{
				EQPivotField objEQPivotField = objIEnumerator.Current as EQPivotField;
				Excel.PivotField objXlPivotField = objPivotTable.PivotFields(objEQPivotField.PivotFieldName) as Excel.PivotField;
				objXlPivotField.Orientation = objEQPivotField.PivotFieldOrientation;
			}
		}


		/// <summary>
		/// Set subtotal, display style of pivot field
		/// </summary>
		/// <param name="objXlPivotField"></param>
		/// <param name="objEQPivotField"></param>
		private void SetPivotFieldStyle(Excel.PivotField objXlPivotField, EQPivotField objEQPivotField)
		{
			if(objEQPivotField.Style == null)
				return;

			//If it's hidden field, return without any operation here
			if(objEQPivotField.PivotFieldOrientation == Excel.XlPivotFieldOrientation.xlHidden || objEQPivotField.PivotTableSelectionMode == EQPivotTableSelectionMode.Default)
				return;

			//Set subtotal style
			for(int i=1; i<=12; i++)
				objXlPivotField.set_Subtotals(i, objEQPivotField.Subtotals[i]);

			//Selection destination area and set their style
			Excel.Range objRange = null;
			if(objEQPivotField.PivotTableSelectionMode == EQPivotTableSelectionMode.DataOnly)
				objRange = objXlPivotField.DataRange;
			else if(objEQPivotField.PivotTableSelectionMode == EQPivotTableSelectionMode.LabelOnly)
				objRange = objXlPivotField.LabelRange;
			else if(objEQPivotField.PivotTableSelectionMode == EQPivotTableSelectionMode.DataAndLabel)
			{
				objRange = objXlPivotField.LabelRange;
				objRange.Application.Union(objRange, objXlPivotField.DataRange, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing,
					this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing, this.Missing,this.Missing);
			}

			objEQPivotField.Style.Output(objRange);
		}
	}
}